﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Base.DAL;
using MPSystem.Execl;
using MPSystem.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Base.BLL
{
    /// <summary>
    /// 顶板离层数据服务
    /// </summary>
    class DataDblcService
    {
        private static readonly int _perPage = 10000;

        #region 顶板离层在线监测
        /// <summary>
        /// 获取实时数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">true：离层 false：下沉</param>
        /// <returns>所有分站实时数据</returns>
        public CurveDblc GetActive(int cqbh, int xdbh,bool isLC=true)
        {
            CurveDblc rt = new CurveDblc();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.DBLC))
            {
                View_DBLC_Active d = _d as View_DBLC_Active;
                if (d == null) return null;
                if ((isLC && d.CGQLX != "LC") || (!isLC && d.CGQLX != "XC")) continue;
                if (d.CQBH != cqbh || d.XDBH != xdbh) continue;
                
                rt.X.Add(d.LCBH.ToString());
                rt.T.Add(d.LCDATETIME);
                rt.WZ.Add(d.CGQWZ);
                rt.DA.Add(d.AJDSD);
                rt.DB.Add(d.BJDSD);
                //超过规定时间无数据则视为故障
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.LCDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("离线");
                    rt.LA.Add(0);
                    rt.LB.Add(0);
                    rt.LA_B.Add(0);
                    rt.WarnNow = true;
                }
                else
                {
                    rt.S.Add((d.LCDATAA == 0 && d.LCDATAB == 0) ? "故障" : "正常");
                    rt.LA.Add(d.LCDATAA);
                    rt.LB.Add(d.LCDATAB);
                    rt.LA_B.Add(Math.Abs(d.LCDATAA - d.LCDATAB));
                    if (d.SFBJ) rt.WarnNow = true;
                }
            }
            return rt;
            //示例：GetActive(1, 1);
        }
        /// <summary>
        /// 获取报警列表信息
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <returns>包含报警信息的结果对象</returns>
        public CurveDblc GetWarn(int cqbh, int xdbh)
        {
            CurveDblc rt = new CurveDblc();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.DBLC))
            {
                View_DBLC_Active d = _d as View_DBLC_Active;
                if (d == null) return null;
                if (d.CQBH != cqbh || d.XDBH != xdbh) continue;
                //超过规定时间无数据则视为故障
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.LCDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("通讯故障，请检查设备状况");
                    rt.X.Add(d.LCBH.ToString());
                    rt.T.Add(d.LCDATETIME);
                    rt.WZ.Add(d.CGQWZ);
                    rt.LX.Add(d.CGQLX);
                }
                else
                if (d.SFBJ)
                {
                    rt.S.Add("警告");
                    rt.X.Add(d.LCBH.ToString());
                    rt.T.Add(d.LCDATETIME);
                    rt.WZ.Add(d.CGQWZ);
                    rt.LX.Add(d.CGQLX);
                }
            }
            return rt;
        }
        #endregion

        #region 顶板离层/下沉数据查询
        /// <summary>
        /// 查询顶板离层/下沉数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"LCDATAA>=10 AND LCDATAB=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        /// <param name="page">分页页数，从0开始</param>
        /// <param name="perPage">每页数量，默认20</param>
        /// <returns>顶板离层/下沉数据列表</returns>
        public IEnumerable<View_DBLC_Active> Query(int cqbh, int xdbh, bool isLC, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj, int page, int perPage = 120)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT LCDATETIME,LCBH,CGQWZ,AJDSD,BJDSD,LCDATAA,LCDATAB,SFBJ
                            FROM (SELECT ROW_NUMBER() OVER(ORDER by LCDATETIME) ROWID,@tb.LCDATETIME,@tb.LCBH,DBLCCGQWZB.CGQWZ,DBLCCGQWZB.AJDSD,DBLCCGQWZB.BJDSD,@tb.LCDATAA,@tb.LCDATAB,@tb.SFBJ
                                FROM @tb INNER JOIN DBLCCGQWZB ON DBLCCGQWZB.CGQBH=@tb.LCBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND DBLCCGQWZB.XDBH=@xdbh AND LCBH IN @bhs
                                AND DBLCCGQWZB.CGQLX='@lx' AND LCDATETIME>=@dtStart AND LCDATETIME<=@dtEnd AND (@where)
                            ) Tb WHERE Tb.ROWID BETWEEN @page*@perPage+1 AND (@page+1)*@perPage";

            sql = sql.Replace("@tb", jq == 0 ? "DBLCSJB" : "DBLCSJB_JQ" + jq);
            sql = sql.Replace("@lx", isLC ? "LC" : "XC");
            string extra = where == null || where == string.Empty ? "1=1" : where;
            if (sfbj >= 0)
            {
                extra += " AND SFBJ=@sfbj";
            }
            sql = sql.Replace("@where", extra);
            return Db.Query<View_DBLC_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd, sfbj = sfbj, page = page, perPage = perPage });
        }
        /// <summary>
        /// 获取顶板离层/下沉曲线数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bh">传感器编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <returns>顶板离层/下沉数据列表</returns>
        public IEnumerable<View_DBLC_Active> CurveNormal(int cqbh, int xdbh, bool isLC, int bh, DateTime dtStart, DateTime dtEnd, int jq)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT @tb.LCDATETIME,@tb.LCBH,DBLCCGQWZB.CGQWZ,DBLCCGQWZB.AJDSD,DBLCCGQWZB.BJDSD,@tb.LCDATAA,@tb.LCDATAB,@tb.SFBJ
                                FROM @tb INNER JOIN DBLCCGQWZB ON DBLCCGQWZB.CGQBH=@tb.LCBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND DBLCCGQWZB.XDBH=@xdbh AND LCBH=@bh
                                AND DBLCCGQWZB.CGQLX='@lx' AND LCDATETIME>=@dtStart AND LCDATETIME<=@dtEnd";

            sql = sql.Replace("@tb", jq == 0 ? "DBLCSJB" : "DBLCSJB_JQ" + jq);
            sql = sql.Replace("@lx", isLC ? "LC" : "XC");
            return Db.Query<View_DBLC_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd});
        }
        /// <summary>
        /// 查询顶板离层/下沉数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"LCDATAA>=10 AND LCDATAB=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        /// <returns>顶板离层/下沉数据列表</returns>
        public int QueryCount(int cqbh, int xdbh, bool isLC, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return 0;
            }
            string sql = @"SELECT COUNT(1) FROM @tb INNER JOIN DBLCCGQWZB ON DBLCCGQWZB.CGQBH=@tb.LCBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND DBLCCGQWZB.XDBH=@xdbh AND LCBH IN @bhs
                                AND DBLCCGQWZB.CGQLX='@lx' AND LCDATETIME>=@dtStart AND LCDATETIME<=@dtEnd AND (@where)";

            sql = sql.Replace("@tb", jq == 0 ? "DBLCSJB" : "DBLCSJB_JQ" + jq);
            sql = sql.Replace("@lx", isLC ? "LC" : "XC");
            string extra = where == null || where == string.Empty ? "1=1" : where;
            if (sfbj >= 0)
            {
                extra += " AND SFBJ=@sfbj";
            }
            sql = sql.Replace("@where", extra);
            return Db.Count(sql, new { cqbh = cqbh, xdbh = xdbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd, sfbj = sfbj});
        }
        #endregion

        #region 顶板离层/下沉数据导出
        /// <summary>
        /// 导出顶板离层/下沉数据
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"LCDATAA>=10 AND LCDATAB=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        /// <returns></returns>
        public void Export(string saveFileName, int cqbh, int xdbh, bool isLC, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory;// System.Environment.CurrentDirectory;
            string tpl = dir + (isLC?@"\Tpl\Query_DBLC.xls": @"\Tpl\Query_DBXC.xls");
            //File.Copy(tpl, saveFileName);
            int page = 0;

            //设置字体，大小，对齐方式
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                sheet.GetRow(1).GetCell(isLC?5:3).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));

                HSSFCellStyle style = ExcelHelper.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                _style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

                int row = 3;
                while (true)
                {
                    var data = Query(cqbh, xdbh,isLC, bhs, dtStart, dtEnd, jq, where, sfbj, page++, _perPage);
                    if (data == null || data.Count() == 0)
                        break;
                    int i = 0;
                    for (; i < data.Count(); i++)
                    {
                        var dataRow = sheet.CreateRow(row + i);
                        int j = 0;
                        var d = data.ElementAt(i);
                        dataRow.CreateCell(j++).SetCellValue(d.LCDATETIME.ToString("yyyy-MM-dd HH:mm:ss"));
                        dataRow.CreateCell(j++).SetCellValue(d.LCBH);
                        dataRow.CreateCell(j++).SetCellValue(d.CGQWZ);
                        if (isLC)
                        {
                            dataRow.CreateCell(j++).SetCellValue(d.AJDSD);
                            dataRow.CreateCell(j++).SetCellValue(d.BJDSD);
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAA == 0 ? "--" : d.LCDATAA.ToString("f2"));
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAB == 0 ? "--" : d.LCDATAB.ToString("f2"));
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAA* d.LCDATAB == 0 ? "--" : Math.Abs(d.LCDATAA- d.LCDATAB).ToString("f2"));
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAA * d.LCDATAB == 0 ? "故障" : (d.SFBJ ? "报警" : "正常"));
                        }
                        else
                        {
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAA == 0 ? "--" : d.LCDATAA.ToString("f2"));
                            dataRow.CreateCell(j++).SetCellValue(d.LCDATAA == 0 ? "故障" : (d.SFBJ ? "报警" : "正常"));
                        }
                        for (j--; j >= 0; j--)
                        {
                            if (j >= 3)
                            {
                                dataRow.GetCell(j).CellStyle = _style;
                            }
                            else
                            {
                                dataRow.GetCell(j).CellStyle = style;
                            }
                        }
                    }
                    row = row + i;
                }
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }

        /// <summary>
        /// 顶板离层/下沉报表参数
        /// </summary>
        public class ExportParam
        {
            public string ComanpyName = "";//公司名称
            public string XD = "";//巷道名称
            public string RZBH = "";//认证编号
            public bool WithChart = false;//是否生成曲线
            public double WarnLimit = 8;//报警值
            public string Sign = "";//签名
        }

        /// <summary>
        /// 导出顶板离层/下沉报表
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="param">报表参数</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bhs">压力分站编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="oneWarn">是否只输出最后一条报警值</param>
        /// <returns>返回顶板离层/下沉报表所需所有数据</returns>
        public void ExportReport(string saveFileName, ExportParam param, int cqbh, int xdbh, bool isLC, int[] bhs, DateTime dtStart, DateTime dtEnd, bool oneWarn = true)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory; //System.Environment.CurrentDirectory;
            string tpl = dir + (isLC ? @"\Tpl\Report_DBLC" : @"\Tpl\Report_DBXC");
            tpl += param.WithChart ? "_Chart.xls" : ".xls";
            //File.Copy(tpl, saveFileName);
            //图表下的第一行
            int rowAfterChart = param.WithChart ? 10 : 3;
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                //创建基本样式
                HSSFCellStyle style = ExportExcel.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                IDataFormat dataformat = workbook.CreateDataFormat();
                _style.DataFormat = dataformat.GetFormat("0.0");
                //修改基本参数
                sheet.GetRow(0).GetCell(0).SetCellValue(param.ComanpyName);
                sheet.GetRow(1).GetCell(6).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));
                sheet.GetRow(2).GetCell(0).SetCellValue(
                    string.Format("巷道名称:{0}   认证编号:{1}   监测日期:{2}至{3}",
                    param.XD, param.RZBH, dtStart.ToString("yyyy-MM-dd HH:mm:ss"), dtEnd.ToString("yyyy-MM-dd HH:mm:ss"))
                );
                //签名
                sheet.GetRow(rowAfterChart + (isLC?22:20)).GetCell(0).SetCellValue(param.Sign);

                //修改起始、终止时间
                if (isLC)
                {
                    sheet.GetRow(rowAfterChart + 1).GetCell(4).SetCellValue(dtStart.ToString("yyyy-MM-dd HH:mm:ss"));
                    sheet.GetRow(rowAfterChart + 1).GetCell(6).SetCellValue(dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                //填写监测数据统计表
                var rt = Report(cqbh, xdbh,isLC, bhs, dtStart, dtEnd, oneWarn);
                int rowList = rowAfterChart + (isLC?3:2);
                IRow mySourceRow = sheet.GetRow(rowList);
                ExcelHelper.InsertRow(sheet, rowList, rt.CGQBH.Count() - 1, mySourceRow);
                for (int i = 0; i < rt.CGQBH.Count(); i++)
                {
                    int j = 0;
                    var dataRow = sheet.GetRow(rowList + i);
                    dataRow.CreateCell(j++).SetCellValue(rt.CGQBH[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.WZ[i]);
                    if (isLC)
                    {
                        dataRow.CreateCell(j++).SetCellValue(rt.AJDSD[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.BJDSD[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LAStart[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LBStart[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LAEnd[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LBEnd[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LA_BEnd[i]);
                    }
                    else
                    {
                        dataRow.CreateCell(j++).SetCellValue(rt.LAStart[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LAEnd[i]);
                        dataRow.CreateCell(j++).SetCellValue(rt.LAEnd[i]- rt.LAStart[i]);
                    }
                    if (param.WithChart)
                    {
                        dataRow.CreateCell(j++).SetCellValue(param.WarnLimit);
                    }
                    for (j--; j >= 0; j--)
                    {
                        if (j >= 2)
                        {
                            dataRow.GetCell(j).CellStyle = _style;
                        }
                        else
                        {
                            dataRow.GetCell(j).CellStyle = style;
                        }
                    }
                }
                //报警信息
                rowList = rowAfterChart + (isLC ? 3 : 2) + rt.CGQBH.Count() + 3;
                mySourceRow = sheet.GetRow(rowList);
                ExcelHelper.InsertRow(sheet, rowList, rt.Warn.Count() - 1, mySourceRow);
                for (int i = 0; i < rt.Warn.Count(); i++)
                {
                    int j = 0;
                    var dataRow = sheet.GetRow(rowList + i);
                    var w = rt.Warn[i];
                    dataRow.CreateCell(j++).SetCellValue(w.LCBH);
                    if (isLC)
                    {
                        dataRow.CreateCell(j++);
                        sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    }
                    dataRow.CreateCell(j++).SetCellValue(w.CGQWZ);
                    if (isLC)
                    {
                        dataRow.CreateCell(j++);
                        sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    }
                    dataRow.CreateCell(j++).SetCellValue(param.WarnLimit);
                    if (isLC)
                    {
                        dataRow.CreateCell(j++).SetCellValue(w.LCDATAA);
                        dataRow.CreateCell(j++).SetCellValue(w.LCDATAB);
                    }
                    else
                    {
                        dataRow.CreateCell(j++).SetCellValue(w.LCDATAA);
                    }
                    dataRow.CreateCell(j++).SetCellValue(w.LCDATETIME.ToString("yyyy-MM-dd HH:mm:ss"));
                    if (isLC)
                    {
                        dataRow.CreateCell(j++);
                        sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    }
                    for (j--; j >= 0; j--)
                    {
                        if (j >= 2)
                        {
                            dataRow.GetCell(j).CellStyle = _style;
                        }
                        else
                        {
                            dataRow.GetCell(j).CellStyle = style;
                        }
                    }
                }

                //保存
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }
        #endregion

        #region 顶板离层/下沉监测报表
        /// <summary>
        /// 获取顶板离层/下沉报表
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="isLC">传感器类别，true:顶板离层 false:顶板下沉</param>
        /// <param name="bhs">压力分站编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="oneWarn">是否只输出最后一条报警值</param>
        /// <returns>返回顶板离层/下沉报表所需所有数据</returns>
        public ReportDblc Report(int cqbh, int xdbh, bool isLC, int[] bhs, DateTime dtStart, DateTime dtEnd,bool oneWarn=true)
        {
            //对分站编号进行排序
            Array.Sort(bhs);
            string sqlTpl = @"SELECT @limit @tb.LCDATETIME,@tb.LCBH,DBLCCGQWZB.CGQWZ,DBLCCGQWZB.AJDSD,DBLCCGQWZB.BJDSD,@tb.LCDATAA,@tb.LCDATAB,@tb.SFBJ
                                FROM @tb INNER JOIN DBLCCGQWZB ON DBLCCGQWZB.CGQBH=@tb.LCBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND DBLCCGQWZB.XDBH=@xdbh AND @condbh
                                AND DBLCCGQWZB.CGQLX='@lx' AND LCDATETIME>=@dtStart AND LCDATETIME<=@dtEnd AND (@where)
                                @order";
            sqlTpl = sqlTpl.Replace("@tb",  "DBLCSJB" );
            sqlTpl = sqlTpl.Replace("@lx", isLC ? "LC" : "XC");
            string sql = sqlTpl.Replace("@limit", "TOP 1").Replace("@condbh", "LCBH=@bh").Replace("@where", "1=1").Replace("@order", "ORDER BY LCDATETIME");
            ReportDblc rt = new ReportDblc();
            foreach (int bh in bhs)
            {
                //起始AB值
                var d = Db.Find<View_DBLC_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
                if (d == null) continue;
                rt.CGQBH.Add(bh);
                rt.WZ.Add(d.CGQWZ);
                rt.AJDSD.Add(d.AJDSD);
                rt.BJDSD.Add(d.BJDSD);
                rt.LAStart.Add(d.LCDATAA);
                rt.LBStart.Add(d.LCDATAB);
                //当前AB值
                d = Db.Find<View_DBLC_Active>(sql + " DESC", new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
                rt.LAEnd.Add(d.LCDATAA);
                rt.LBEnd.Add(d.LCDATAB);
                rt.LA_BEnd.Add(Math.Abs(d.LCDATAA-d.LCDATAB));
            }
            //查找报警值
            string limit = oneWarn ? "ROW_NUMBER() OVER (PARTITION BY LCBH ORDER BY LCDATETIME DESC) as ROWID," : "";
            sql = sqlTpl.Replace("@limit", limit).Replace("@condbh", "LCBH in @bhs").Replace("@where", "SFBJ=1").Replace("@order","");
            if(oneWarn)
                sql = "SELECT TB.* FROM (" + sql + ") TB WHERE TB.ROWID=1";
            rt.Warn.AddRange(Db.Query<View_DBLC_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd }));
            return rt;
        }
        #endregion
        
        public static void Test()
        {
            var rt=new DataDblcService().GetActive(1, 1);
            IEnumerable<View_DBLC_Active>  list= new DataDblcService().Query(1, 1, true,new int[]{1,2,3,4,5}, new DateTime(2018, 12, 23), new DateTime(2018, 12, 24),0,"",-1,0);
            ReportDblc report = new DataDblcService().Report(1, 1,true, new int[]{1,2,3,4,5},new DateTime(2018, 12, 23), new DateTime(2018, 12, 24));
            Console.WriteLine();
        }
    }
    
    #region 保存结果的类
    /// <summary>
    /// 顶板离层/下沉查询返回结果，用于绘图
    /// </summary>
    class CurveDblc
    {
        /// <summary>
        /// 是否报警
        /// </summary>
        public bool WarnNow = false;
        /// <summary>
        /// 时间
        /// </summary>
        public List<DateTime> T = new List<DateTime>();
        /// <summary>
        /// 横轴标记
        /// </summary>
        public List<string> X = new List<string>();
        /// <summary>
        /// 传感器类型,LC为离层，XC为下沉
        /// </summary>
        public List<string> LX = new List<string>();
        /// <summary>
        /// 工作状态
        /// </summary>
        public List<string> S = new List<string>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        /// <summary>
        /// A点深度
        /// </summary>
        public List<double> DA = new List<double>();
        /// <summary>
        /// B点深度
        /// </summary>
        public List<double> DB = new List<double>();
        #region 位移序列
        public List<double> LA = new List<double>();
        public List<double> LB = new List<double>();
        public List<double> LA_B = new List<double>();
        #endregion
    }
    /// <summary>
    /// 顶板离层/下沉报表
    /// </summary>
    public class ReportDblc
    {
        /// <summary>
        /// 传感器编号
        /// </summary>
        public List<int> CGQBH = new List<int>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        /// <summary>
        /// A基点深度
        /// </summary>
        public List<double> AJDSD = new List<double>();
        /// <summary>
        /// B基点深度
        /// </summary>
        public List<double> BJDSD = new List<double>();
        /// <summary>
        /// 起始A值
        /// </summary>
        public List<double> LAStart = new List<double>();
        /// <summary>
        /// 起始B值
        /// </summary>
        public List<double> LBStart = new List<double>();
        /// <summary>
        /// 当前A值
        /// </summary>
        public List<double> LAEnd = new List<double>();
        /// <summary>
        /// 当前B值
        /// </summary>
        public List<double> LBEnd = new List<double>();
        /// <summary>
        /// 当前A-B绝对值
        /// </summary>
        public List<double> LA_BEnd = new List<double>();
        /// <summary>
        /// 报警列表
        /// </summary>
        public List<View_DBLC_Active> Warn = new List<View_DBLC_Active>();
    }
    #endregion
}
